package com.lagou.task.dao;

import com.lagou.task.entity.Student;
import com.lagou.task.utils.Dbutils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;

public class StudentDaoImpl implements StudentDao{
    /**
     * 添加学生信息
     * @param student
     * @return
     */
    @Override
    public int addStudent(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = Dbutils.getConnection();
            String sql = "insert into student values (?,?,?,?,?,?,?)";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,student.getId());
            preparedStatement.setString(2,student.getName());
            preparedStatement.setString(3,student.getSex());
            preparedStatement.setString(4,student.getBirth());
            preparedStatement.setString(5,student.getEmail());
            preparedStatement.setString(6,student.getNote());
            preparedStatement.setString(7,student.getClassName());
            int update = preparedStatement.executeUpdate();
            return update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Dbutils.close(connection,preparedStatement,resultSet);
        }
        return 0;
    }

    /**
     * 根据学号删除学生信息
     * @param student
     * @return
     */
    @Override
    public int deleteStudent(Student student) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = Dbutils.getConnection();
            String sql = "delete from student where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,student.getId());
            int update = preparedStatement.executeUpdate();
            return update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Dbutils.close(connection,preparedStatement,resultSet);
        }
        return 0;
    }

    /**
     * 实现根据学号修改学生信息
     * @param modifyStudent
     * @return
     */
    @Override
    public int modifyStudent(Student modifyStudent) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = Dbutils.getConnection();
            String sql = "update student set name=? , sex=? , birth=? , email=? , note=? , className = ? where id=?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,modifyStudent.getName());
            preparedStatement.setString(2,modifyStudent.getSex());
            preparedStatement.setString(3,modifyStudent.getBirth());
            preparedStatement.setString(4,modifyStudent.getEmail());
            preparedStatement.setString(5,modifyStudent.getNote());
            preparedStatement.setString(6,modifyStudent.getClassName());
            preparedStatement.setString(7,modifyStudent.getId());
            int update = preparedStatement.executeUpdate();
            return update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Dbutils.close(connection,preparedStatement,resultSet);
        }
        return 0;
    }

    /**
     * 实现根据学号查找学生信息
     * @param studentId
     * @return
     */
    @Override
    public Student findStudent(String studentId) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = Dbutils.getConnection();
            String sql = "select * from student where id = ?";
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setString(1,studentId);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()){
                Student student = new Student(resultSet.getString("id"),
                        resultSet.getString("name"),
                        resultSet.getString("sex"),
                        String.valueOf(resultSet.getDate("birth")),
                        resultSet.getString("email"),
                        resultSet.getString("note"),
                        resultSet.getString("className"));
                return student;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Dbutils.close(connection,preparedStatement,resultSet);
        }
        return null;
    }

    /**
     * 实现查询所有学生信息操作
     * @return
     */
    @Override
    public List<Student> showAll() {
        List<Student> students = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = Dbutils.getConnection();
            String sql = "select * from student";
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            if (!resultSet.wasNull()){
                while (resultSet.next()){
                    Student student = new Student(resultSet.getString("id"),
                            resultSet.getString("name"),
                            resultSet.getString("sex"),
                            String.valueOf(resultSet.getDate("birth")),
                            resultSet.getString("email"),
                            resultSet.getString("note"),
                            resultSet.getString("className"));
                    students.add(student);
                }
                students.sort(Comparator.comparingInt(x -> Integer.parseInt(x.getId())));
                return students;
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            Dbutils.close(connection,preparedStatement,resultSet);
        }
        return null;
    }
}
